﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Entity;
using School.Entities;

namespace School.Common
{
    public static class Structure
    {
        private static SchoolEntities db = new SchoolEntities();

        public static IEnumerable<ORG_Campus> GetCampuses()
        {
            return (from c in db.ORG_Campus orderby c.Name select c).ToList();
        }

        public static IEnumerable<ORG_Certificate> GetCertificates()
        {
            return (from c in db.ORG_Certificate orderby c.Name select c).ToList();
        }

        public static IEnumerable<ORG_Stage> GetStages()
        {
            return (from s in db.ORG_Stage orderby s.Code select s).ToList();
        }
        public static IEnumerable<LU_District> GetAllDistrict()
        {
            return (from d in db.LU_District orderby d.Name select d).ToList();

        }
        public static IEnumerable<ORG_Grade> GetGrades()
        {
            return (from g in db.ORG_Grade orderby g.Code select g).ToList();
        }
        public static IEnumerable<ORG_Class> GetClasses()
        {
            return (from c in db.ORG_Class orderby c.Code ascending select c).ToList();
        }

        /*public static IEnumerable<ORG_Class> GetClasses()
        {
            return (from g in db.ORG_Class orderby g.Name select g);
        }*/


        public static IQueryable<ORG_Certificate> GetCertificatesByCampus(long campus_id)
        {
            return from c in db.ORG_Certificate
                   where c.Campus_Id == campus_id
                   orderby c.Name
                   select c;
        }
        public static string get_Certi_Code(int cer_id)
        {
            return ((from c in db.ORG_Certificate where c.Id == cer_id select c.Code).FirstOrDefault());

        }

        public static IQueryable<ORG_Stage> GetStagesByCertificate(long certificate_id)
        {
            return from s in db.ORG_Stage
                   where s.Certificate_Id == certificate_id
                   orderby s.Name
                   select s;
        }
        public static string Get_stage_code(int sta_id)
        {
            return ((from s in db.ORG_Stage where s.Id == sta_id select s.Code).FirstOrDefault());
        }
        public static IQueryable<ORG_Grade> GetGradesByStage(long stage_id)
        {
            return from g in db.ORG_Grade
                   where g.Stage_Id == stage_id
                   orderby g.Name
                   select g;
        }
        public static string get_Grad_Code(int grad_id)
        {
            return ((from g in db.ORG_Grade where g.Id == grad_id select g.Code).FirstOrDefault());
        }
        public static IQueryable<LU_District> GetDistrictByCamp(long campus_id)
        {
            var result = from c in db.LU_District
                         where c.Campus_Id == campus_id
                         orderby c.Name
                         select c;
            return result;
        }

        public static long GetPayment_Order_Number()
        {
            var Update = (from c in db.ACC_Counters where c.ORG_EductionalYear.IsCurrent == true select c).FirstOrDefault();
            long x = Update.Payment_Order_Number += 1;
            db.SaveChanges();
            return x;
        }


        public static long GetReq_Number()
        {

            var Update = (from c in db.ACC_Req_Number select c).FirstOrDefault();

            if (Update != null)
            {
                long x = Update.reqno += 1;
                db.SaveChanges();
                return x;
            }

            else
            {
                db.AddToACC_Req_Number(new ACC_Req_Number() { reqno = 1 });
                db.SaveChanges();
                return 1;


            }
        }

        public static IQueryable<ORG_EductionalYear> GetCurrentEductionalYear()
        {
            var result = from c in db.ORG_EductionalYear
                         where c.IsCurrent == true
                         select c;
            return result;
        }

        public static IQueryable<ORG_Class> GetClassesByGrade(long Grade_Id)
        {
            var result = from c in db.ORG_Class
                         where c.Grade_Id == Grade_Id
                         select c;
            return result;
        }

        public static int GetGradeCapacity(long id)
        {
            return int.Parse((from x in db.ORG_Class where x.Grade_Id == id select x.Capacity).Sum().ToString());
        }

        public static int GetAcceptedStudentsByGrade(long id)
        {
            return (from x in db.ORG_Class where x.Grade_Id == id select x).Count();
        }

        public static long GetVoucherNumber()
        {
            var Update = (from c in db.ACC_Counters where c.ORG_EductionalYear.IsCurrent == true select c).FirstOrDefault();
            long x = Update.Voucher_Number += 1;
            db.SaveChanges();
            return x;

        }
    }
}
